﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;

namespace ExcelToData
{
    internal class OracleHelper
    {
        private static string connectionString;
        //private static string connectionString = "Data Source =(local); Initial Catalog = Test; User ID = sa; Password=sa";
        public OracleHelper()
        {
            connectionString = AppCode.connectionString;
        }

        public static Boolean IsConNet()
        {
            bool Flag = false;
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    if (conn.State == ConnectionState.Open)
                    {
                        Flag = true;
                    }
                }
                catch
                {
                    Flag = false;
                }
            }
            return Flag;
        }

        public static string TableByData(DataTable dt, String TableName)
        {
            System.Diagnostics.Stopwatch st = new System.Diagnostics.Stopwatch();
            st.Start();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                if (!IsExistTable(TableName))
                {
                    string strSql = "Create Table " + TableName + "(";
                    foreach (DataColumn col in dt.Columns)
                    {
                        strSql += "[" + col + "] varchar(max),";
                    }
                    strSql = strSql.LastIndexOf(",") == strSql.Length - 1 ? strSql.Remove(strSql.Length - 1, 1) : strSql;
                    strSql += ")";
                    AppCode.WriteLog(strSql, false);
                    ExeCuteSql(strSql);
                }

                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(conn))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = TableName;
                        foreach (DataColumn col in dt.Columns)
                        {
                            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                        }
                        bulkCopy.WriteToServer(dt);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
            st.Stop();
            return st.ElapsedMilliseconds.ToString();
        }

        public static Boolean IsExistTable(string TableName)
        {
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();

                //string isExitTableSql = @"select * from dbo.sysobjects where name = '" + TableName + @"'";
                string isExitTableSql = @"select * from dbo.sysobjects where id = object_id(N'" + TableName + "') and (type='U' or type='S')";
                OracleCommand cmd = new OracleCommand(isExitTableSql, conn);
                object pResult = cmd.ExecuteScalar();
                if (pResult == null || pResult == System.DBNull.Value) return false;
                return true;
            }
        }

        public static Boolean ExeCuteSql(String strSql)
        {
            bool Flag = false;
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                using (OracleCommand cmd = new OracleCommand(strSql, conn))
                {
                    conn.Open();
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    Flag = true;
                }
            }
            return Flag;
        }
    }
}
